import { ConnectionConfig, createPool, Pool, PoolConfig } from "mysql"
import { Util } from "../lib/Util";

function toLen(num: number, len: number) {
    let out = num.toString();
    while (out.length < len) out = '0' + out;
    return out;
}

function formateString(this: Date) {
    return `${toLen(this.getFullYear(), 4)}-${toLen(this.getMonth() + 1, 2)}-${toLen(this.getDate(), 2)} ${toLen(this.getHours(), 2)}:${toLen(this.getMinutes(), 2)}:${toLen(this.getSeconds(), 2)}`;
}

export function _subLine_value_(key: string, value: any) {
    // 说明内容是简单模式了，直接操作
    // 如果是天选字段那么需要返回
    if (value === undefined) value = null;
    let outStr = "";
    switch (key) {
        case "$exists": {
            outStr = ` ${value ? "is not null" : "is null"}`;
            break
        }
        case '$ne': outStr = ` !="${value}"`; break;
        case '$gt': outStr = ` ="${value}"`; break;
        case '$gte': outStr = ` >="${value}"`; break;
        case '$lt': outStr = ` <"${value}"`; break;
        case '$lte': outStr = ` <="${value}"`; break;
        case '$in': {
            if (value instanceof Array) {
                outStr = ` in ("${value.join('" , "')}")`;
            }
            break;
        }
        case '$or': {
            let result: string[] = []
            for (let key in value) {
                result.push(_subLine_value_(key, value[key]))
            }
            outStr = '(' + result.join(' or ') + ')';
            break;
        }
        case '$and': {
            let result: string[] = []
            for (let key in value) {
                result.push(_subLine_value_(key, value[key]))
            }
            if (result.length <= 1) {
                outStr = ` ${result[0] || ""} `
            }
            else {
                outStr = '(' + result.join(' and ') + ')';
            }
            break;
        }
        default:
            if (value === null) {
                return `\`${key}\` is null`
            }
            else {
                if (typeof value == "object") {
                    outStr = `\`${key}\`${_subLine_value_("$and", value)}`
                }
                else {
                    outStr = `\`${key}\`="${value}"`
                }
            }
            break;
    }

    return outStr
}

export function toWhere(sub_find: { [x: string]: any }): string {
    return _subLine_value_("$and", sub_find);
}

function parseType(sType: string) {
    // 查询一下是否有括号
    let list = sType.split(/\(|\)/g)
    list[1] = list[1] || "0"
    list[2] = list[2] || ""

    let result = { type: list[0], enums: [] as string[], size: parseInt(list[1]), unsigned: list[2].indexOf("unsigned") >= 0, zerofill: list[2].indexOf("zerofill") >= 0 }
    if (isNaN(result.size)) {
        result.size = 0;
    }
    switch (result.type.toUpperCase()) {
        case 'NULL':
        case 'GEOMETRY':
            result.type = "any";
            break;
        case 'DECIMAL':
        case 'TINY':
        case 'SHORT':
        case 'LONG':
        case 'FLOAT':
        case 'DOUBLE':
        case 'LONGLONG':
        case 'INT24':
        case 'INT':
        case 'YEAR':
        case 'NEWDECIMAL':
        case 'TINYINT':
            result.type = "number";
            break;
        case 'CHAR':
        case 'VARCHAR':
        case 'JSON':
        case 'VAR_STRING':
        case 'STRING':
        case 'TEXT':
        case 'LONGTEXT':
            result.type = "string";
            break;
        case 'ENUM':
        case 'SET':
            result.enums = list[1].split(',')
            result.size = 0;
            result.type = "string";
            break;
        case 'TIME2':
        case 'NEWDATE':
        case 'DATE':
        case 'TIME':
        case 'DATETIME':
        case 'DATETIME2':
        case 'TIMESTAMP':
        case 'TIMESTAMP2':
            result.type = "Date";
            break;
        case 'BIT':
        case "BINARY":
        case "VARBINARY":
        case 'TINY_BLOB':
        case 'MEDIUM_BLOB':
        case 'LONG_BLOB':
        case 'BLOB':
            result.type = "Buffer";
            break;
        default:
            result.type = "string";
            break;
    }

    return result;
}

function parseNull(sNull: string) {
    if (sNull == "YES") {
        return true
    }
    return false;
}

function parseKey(sKey: string) {
    if (sKey == "PRI") {
        return true
    }
    return false;
}

type FieldStruct = {
    Default: any,
    Extra: string,
    Field: string,
    Key: boolean
    Null: boolean
    Type: {
        type: string,
        size: number,
        unsigned: boolean,
        zerofill: boolean
    }
}

// 生成结构
function toInterface(table: string, list: FieldStruct[]) {
    let results: string[] = []
    for (let i = 0; i < list.length; i++) {
        let l = list[i];
        let str = `${l.Field}${l.Null ? '?' : ''}: ${l.Type.type}`;
        if (l.Key) {
            results.unshift(str)
        }
        else {
            results.push(str)
        }
    }

    return "interface TableDef" + table + "{\n\t" + results.join(",\n\t") + "\n}"
}



// 加载表格的类型，通过interface内容
function loadTableInterface(file: string) {
    // 这里先不弄了
}

export class MysqlConn {
    client!: Pool

    tableFields: { [table: string]: { [filed: string]: FieldStruct } } = {}

    // 初始化数据库
    async init(host: string, port: number, databse: string, user: string, passwd: string, connectTimeout?: number, timezone?: string) {
        await this.initConnect(host, port, databse, user, passwd, connectTimeout, timezone);
        let tables = await this.loadTables();
        for (let i = 0; i < tables.length; i++) {
            await this.initQueryTable(tables[i])
        }

        return true;
    }

    private async loadTables() {
        let tables = await this.query<{ Table_type: string, [x: string]: string }>("show full tables")
        // console.log(tables)
        let result: string[] = [];
        let Showkey = "Tables_in_" + this.client.config.database
        for (let i = 0; i < tables.length; i++) {
            let res = tables[i]
            // if (res.Table_type == "BASE TABLE") {
            result.push(res[Showkey])
            // }
        }

        return result;
    }

    private async initQueryTable(table: string) {
        let d = await this.query<{
            Default: any,
            Extra: string,
            Field: string,
            Key: string
            Null: string
            Type: string
        }>("DESCRIBE " + table)

        let result: { [x: string]: FieldStruct } = {}
        for (let i = 0; i < d.length; i++) {
            result[d[i].Field] = {
                Default: d[i].Default,
                Extra: d[i].Extra,
                Field: d[i].Field,
                Key: parseKey(d[i].Key),
                Null: parseNull(d[i].Null),
                Type: parseType(d[i].Type)
            }
        }

        // console.log(toInterface(table, result))

        this.tableFields[table] = result
    }

    private initConnect(host: string, port: number, databse: string, user: string, passwd: string, connectTimeout?: number, timezone?: string) {
        let cfg: PoolConfig = {
            host: host,
            port: port,
            timezone: timezone,
            connectTimeout: connectTimeout,
            database: databse,
            user: user,
            password: passwd
        }
        this.client = createPool(cfg)
        return new Promise<boolean>((r, j) => {
            this.client.getConnection((err, conn) => {
                if (err) {
                    console.error(err)
                    j(false)
                }
                else {
                    this.client.config.database = (this.client.config.database || (this.client.config as any)["connectionConfig"].database)
                    conn.release();
                    r(true)
                }
            })
        })
    }

    // 插入数据
    async insert(table: string, info: { [x: string]: any }) {
        let fields = this.tableFields[table];
        if (!fields) throw Util.makeError(1, `table[${table}] is no exist`);

        // 这里需要操作
        let insertKeys: string[] = [];
        let insertValues: string[] = []
        for (let key in fields) {
            let value = this.checkFileds(table, key, info[key])
            if (value === undefined || value === null) {
                continue;
            }
            insertKeys.push(`\`${key}\``);
            insertValues.push(this.toSqlStr(fields[key].Type.type, value))
        }

        let result = await this.query<{
            affectedRows: number
            changedRows: number
            fieldCount: number
            insertId: number
            message: string
            protocol41: boolean
            serverStatus: number
            warningCount: number
        }>(`insert into ${table} (${insertKeys.join(',')}) values (${insertValues.join(',')});`)

        if (!result || !result[0]) {
            // 如果失败了那么返回一个异常，一般不会出现的
            throw Util.makeError(1, `result is undefined`)
        }

        return result[0].insertId
    }

    query<T>(sql: string) {
        return new Promise<T[]>((resolve, reject) => {
            let f_sql = this.client.query(sql)


            let result: T[] = [];

            f_sql.on("result", function (row: any, index: number) {
                if (index != 0) {
                    // 这里最好提示一下
                    console.log("f_sql.on result", index);
                }
                // 这里最好把属性剥离，只留下内容
                result.push(Util.copy(row));
            })

            f_sql.on("error", function (err) {
                reject(err)
            })

            f_sql.on("end", function () {
                resolve(result)
            })

            // f_sql.on("packet", function () {
            //     console.log(...arguments)
            // })

            // f_sql.on("fields", function () {
            //     console.log(...arguments)
            // })
        })
    }

    // 检查字段是否合法
    checkFileds(table: string, key: string, value: any) {
        if (!this.tableFields[table]) throw Util.makeError(1, `table[${table}] is no exist`);
        let ff = this.tableFields[table];
        if (!ff[key]) throw Util.makeError(1, `table[${table}] field[${key}] is no exist`);
        let cType = typeof value;
        if (value == undefined || value == null) {
            if (ff[key].Null || ff[key].Extra.indexOf("auto_increment") >= 0) {
                return value
            }

            throw Util.makeError(1, `table[${table}]:value[${key}] is dismatch, target:[${ff[key].Type.type}] current:[${cType}]`)
        }

        // 这里有些特殊的格式需要处理
        switch (ff[key].Type.type) {
            case "Date":
                if (!(value instanceof Date)) {
                    if (typeof value == "number") {
                        value = new Date(value);
                        break;
                    }

                    throw Util.makeError(1, `table[${table}]:value[${key}] is dismatch, target:[${ff[key].Type.type}] current:[${cType}]`)
                }
                break;
            case "string": {
                // 这里增加一些回旋的余地
                if (ff[key].Type.type != cType) {
                    value = JSON.stringify(value)
                }
                break;
            }
            case "number": {
                if (cType == "string") {
                    value = parseFloat(value)
                    if (isNaN(value)) {
                        throw Util.makeError(1, `table[${table}]:value[${key}] is dismatch, target:[${ff[key].Type.type}] current:[${cType}]`)
                    }
                }
                break;
            }
            case "Buffer": {
                if (!(value instanceof Buffer)) {
                    throw Util.makeError(1, `table[${table}]:value[${key}] is dismatch, target:[${ff[key].Type.type}] current:[${cType}]`)
                }
                break;
            }
            default: {
                if (ff[key].Type.type != cType) {
                    throw Util.makeError(1, `table[${table}]:value[${key}] is dismatch, target:[${ff[key].Type.type}] current:[${cType}]`)
                }
                break;
            }
        }

        return value;
    }

    toSqlStr(type: string, value: any) {
        switch (type) {
            case "Date":
                return `"${formateString.apply(value)}"`;
            case "Buffer":
                return `"${value.toString()}"`;
            default:
                return `"${value}"`
        }
    }

    // 删除数据
    async delete(table: string, find: { [x: string]: any }) {
        let result = await this.query<{
            affectedRows: number
            changedRows: number
            fieldCount: number
            insertId: number
            message: string
            protocol41: boolean
            serverStatus: number
            warningCount: number
        }>(`delete from ${table} where ${toWhere(find)}`)

        if (!result || !result[0]) return false;
        if (result[0].affectedRows > 0) return true

        return false
    }

    async deleteOnce(table: string, find: { [x: string]: any }) {
        let result = await this.query<{
            affectedRows: number
            changedRows: number
            fieldCount: number
            insertId: number
            message: string
            protocol41: boolean
            serverStatus: number
            warningCount: number
        }>(`delete from ${table} where ${toWhere(find)} limit 1`)

        if (!result || !result[0]) return false;
        if (result[0].affectedRows > 0) return true

        return false
    }

    close() {
        return new Promise<void>((r, j) => {
            this.client.end(function (err) {
                if (err) j()
                else r()
            })
        })
    }
}